﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace ProductManager
{
    static class DBAccess
    {
        private static String _connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=";
        private static OleDbConnection _connection;
        public static OleDbConnection Connection
        {
            get { return _connection; }
            set { _connection = value; }
        }

        private static DataTable ExcuQuery(string sql)
        {
            DataTable datatable = new DataTable();
            OleDbCommand command = _connection.CreateCommand();
            command.CommandText = sql;
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(datatable);
            return datatable;
        }

        private static void ExcuNonQuery(string sql)
        {
            OleDbCommand command = _connection.CreateCommand();
            command.CommandText = sql;
            command.ExecuteNonQuery();
        }

        public static bool OpenConnection()
        {
            return OpenConnection("\\\\DANGNHATDUY-PC\\SJ7-Task\\");
        }

        public static bool OpenConnection(string path)
        {
            try
            {
                _connection = new OleDbConnection(_connectionString + "\"" + path + "ProductManage.mdb\" ;Jet OLEDB:Database Password=saturdayjuly7");
                _connection.Open();
            }
            catch
            {
                return false;
            }
            return true;
        }

        public static void CloseConnection()
        {
            if (_connection != null)
            {
                _connection.Close();
            }
        }

        public static DataTable Search(string table, string keyword, string searchin)
        {
            try
            {
                DataTable products;
                products = ExcuQuery("select * from " + table + " where " + searchin + " like '%" + keyword + "%'");
                return products;
            }
            catch (Exception)
            {
            }
            return null;
        }

        public static DataTable GetRowWithID(string table, string id)
        {
            DataTable product = null;
            try
            {
                product = ExcuQuery("select * from " + table + " where ID = " + id);
            }
            catch (System.Exception ex)
            {
                return null;
            }
            return product;
        }

        public static DataTable GetLastRow(string table)
        {
            DataTable product = null;
            try
            {
                product = ExcuQuery("select top 1 * from " + table + " order by ID desc");
            }
            catch (System.Exception ex)
            {
                return null;
            }
            return product;
        }

        public static DataTable SearchProduct(string keyword, string searchin)
        {
            try
            {
                DataTable products;
                products = ExcuQuery(
                    "select ID, Name, Description, min(Price) as MinPrice from Product left join ProductProvider on Product.ID = ProductProvider.ProductID where " +
                    searchin + " like '%" + keyword + "%' group by ID, Name, Description");
                return products;
            }
            catch (Exception ex)
            {
            }
            return null;
        }

        public static bool AddProduct(string name, string description)
        {
            try
            {
                ExcuNonQuery("insert into Product(Name, Description) values('" +
                    name + "', '" + description + "')");
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }

        public static bool UpdateProduct(string id, string name, string description)
        {
            try
            {
                ExcuNonQuery("update Product set Name = '" + name +
                    "', Description = '" + description +
                    "' where ID = " + id);
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool DeleteProduct(string id)
        {
            try
            {
                ExcuNonQuery("delete from ProductCategory where ProductID = " + id);
                ExcuNonQuery("delete from ProductProvider where ProductID = " + id);
                ExcuNonQuery("delete from Product where ID = " + id);
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool AddProvider(string name, string address, string website, string rating)
        {
            try
            {
                ExcuNonQuery("insert into Provider(Name, Address, Website, Rating) values('" +
                    name + "', '" + address + "', '" + website + "', " + rating + ")");
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }

        public static bool UpdateProvider(string id, string name, string address, string website, string rating)
        {
            try
            {
                ExcuNonQuery("update Provider set Name = '" + name +
                    "', Address = '" + address + "', Website = '" + website +
                    "', Rating = " + rating + " where ID = " + id);
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool DeleteProvider(string id)
        {
            try
            {
                ExcuNonQuery("delete from ProviderCategory where ProviderID = " + id);
                ExcuNonQuery("delete from ProductProvider where ProviderID = " + id);
                ExcuNonQuery("delete from Provider where ID = " + id);
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static DataTable GetChildCategory(string parentID)
        {
            DataTable categories = null;
            try
            {
                categories = ExcuQuery("select ID, Name from Category inner join CategoryRelation on Category.ID = CategoryRelation.ChildID where ParentID = " + parentID);
            }
            catch (System.Exception ex)
            {
                return null;
            }
            return categories;
        }

        public static DataTable GetRootCategory()
        {
            DataTable categories = null;
            try
            {
                categories = ExcuQuery("select ID, Name from Category where ID not in (select ChildID from CategoryRelation)");
            }
            catch (System.Exception ex)
            {
                return null;
            }
            return categories;
        }

        public static DataTable GetAllCategory()
        {
            DataTable categories = null;
            try
            {
                categories = ExcuQuery("select * from Category");
            }
            catch (System.Exception ex)
            {
                return null;
            }
            return categories;
        }

        public static bool AddCategory(string name)
        {
            try
            {
                ExcuNonQuery("insert into Category(Name) values('" + name + "')");
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool RenameCategory(string id, string name)
        {
            try
            {
                ExcuNonQuery("update Category set Name = '" + name + "' where ID = " + id);
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool DeleteCategory(string id)
        {
            try
            {
                ExcuNonQuery("delete from CategoryRelation where ParentID = " + id);
                ExcuNonQuery("delete from CategoryRelation where ChildID = " + id);
                ExcuNonQuery("delete from ProductCategory where CategoryID = " + id);
                ExcuNonQuery("delete from ProviderCategory where CategoryID = " + id);
                ExcuNonQuery("delete from Category where ID = " + id);
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool AddChildCategory(string parentID, string childID)
        {
            try
            {
                ExcuNonQuery("insert into CategoryRelation(ParentID, ChildID) values(" + parentID + ", " + childID + ")");
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static DataTable GetProductCategory(string productID)
        {
            DataTable categories = null;
            try
            {
                categories = ExcuQuery("select ID, Name from Category inner join ProductCategory on Category.ID = ProductCategory.CategoryID where ProductID = " + productID);
            }
            catch (System.Exception ex)
            {
                return null;
            }
            return categories;
        }

        public static bool AddProductCategory(string productID, string categoryID)
        {
            try
            {
                ExcuNonQuery("insert into ProductCategory(ProductID, CategoryID) values(" +
                    productID + ", " + categoryID + ")");
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool DeleteProductCategory(string productID, string categoryID)
        {
            try
            {
                ExcuNonQuery("delete from ProductCategory where ProductID = " +
                    productID + " AND CategoryID = " + categoryID);
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static DataTable GetProductProvider(string productID)
        {
            DataTable providers = null;
            try
            {
                providers = ExcuQuery("select ID, Name, Price from Provider inner join ProductProvider on Provider.ID = ProductProvider.ProviderID where ProductID = " + productID);
            }
            catch (System.Exception ex)
            {
                return null;
            }
            return providers;
        }

        public static DataTable GetProviderProduct(string providerID)
        {
            DataTable products = null;
            try
            {
                products = ExcuQuery("select ID, Name, Price from Product inner join ProductProvider on Product.ID = ProductProvider.ProductID where ProviderID = " + providerID);
            }
            catch (System.Exception ex)
            {
                return null;
            }
            return products;
        }

        public static bool AddProductProvider(string productID, string providerID, string price)
        {
            try
            {
                ExcuNonQuery("insert into ProductProvider(ProductID, ProviderID, Price) values(" +
                    productID + ", " + providerID + ", " + price + ")");
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool DeleteProductProvider(string productID, string providerID)
        {
            try
            {
                ExcuNonQuery("delete from ProductProvider where ProductID = " +
                    productID + " AND ProviderID = " + providerID);
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static DataTable GetProviderCategory(string providerID)
        {
            DataTable categories = null;
            try
            {
                categories = ExcuQuery("select ID, Name from Category inner join ProviderCategory on Category.ID = ProviderCategory.CategoryID where ProviderID = " + providerID);
            }
            catch (System.Exception ex)
            {
                return null;
            }
            return categories;
        }

        public static bool AddProviderCategory(string providerID, string categoryID)
        {
            try
            {
                ExcuNonQuery("insert into ProviderCategory(ProviderID, CategoryID) values(" +
                    providerID + ", " + categoryID + ")");
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }

        public static bool DeleteProviderCategory(string providerID, string categoryID)
        {
            try
            {
                ExcuNonQuery("delete from ProviderCategory where ProviderID = " +
                    providerID + " AND CategoryID = " + categoryID);
            }
            catch (System.Exception ex)
            {
                return false;
            }
            return true;
        }
    }
}
